<?php
/**
 * phpOffice相关操作
 * @author yupoxiong<i@yufuping.com>
 */

namespace app\admin\traits;

use Exception;
use support\Log;
use XLSXWriter;
use think\facade\Db;
use support\Response;
use RuntimeException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Shopwwi\WebmanFilesystem\Facade\Storage;

trait AdminPhpOffice
{
    /**
     * 普通导出，少于1万条数据可使用，1万+数据用exportXlsx
     * @param array $head
     * @param array $body
     * @param string $name
     * @param string $title
     * @return void
     */
    protected function exportData(array $head, array $body, string $name = '', string $title = 'Sheet1'): Response
    {
        try {
            if (empty($name)) {
                $name = date('Y-m-d-H-i-s');
            }

            [$spreadsheet, $sheet, $char_index] = $this->initSheet();

            // Excel 表格头
            foreach ($head as $key => $val) {
                $sheet->setCellValue($char_index[$key] . '1', $val);
            }

            // Excel body 部分
            foreach ($body as $key => $val) {
                $row = $key + 2;
                $col = 0;
                foreach ($val as $v) {
                    $sheet->setCellValue($char_index[$col] . $row, $v);
                    $col++;
                }
            }

            $spreadsheet->getActiveSheet()->setTitle($title);

            $writer = new Xlsx($spreadsheet);

            $file_name = sha1($name . microtime());
            $file_path = base_path() . '/export/' . date('Ym') . '/';
            if (!is_dir($file_path) && !mkdir($file_path, 0777, true) && !is_dir($file_path)) {
                throw new RuntimeException(sprintf('Directory "%s" was not created', $file_path));
            }
            $save_name = $file_path . $file_name . '.xlsx';
            // 保存文件到export目录下
            $writer->save($save_name);
            // 下载文件
            return response()->download($save_name, $name . '.xlsx');

        } catch (Exception $e) {
            return admin_error($e->getMessage() . '(' . $e->getFile() . '-' . $e->getLine() . ')');
        }
    }

    /**
     * 导入数据到数据库
     * @param string $name 文件名
     * @param string $table 表名(不含前缀)
     * @param array $field_list 字段列表
     * @param int $limit 批量插入数据库每次的条数，默认100
     * @param bool $del 导入成功后是否删除文件
     * @return bool|string
     */
    protected function importData(string $name, string $table, array $field_list, int $limit = 100, bool $del = true)
    {
        $config = config('plugin.shopwwi.filesystem.app.storage.admin_import');

        $file = (request())->file($name);

        $result = Storage::adapter('admin_import')
            ->path('/' . date('Ym'))
            ->size($config['validate']['size'])
            ->extYes($config['validate']['ext'])
            ->upload($file);

        $path        = $config['root'] . '/' . $result->file_name;
        $spreadsheet = IOFactory::load($path);
        $excel_array = $spreadsheet->getActiveSheet()->toArray();
        array_shift($excel_array);
        if (empty($excel_array)) {
            return '导入文件数据为空';
        }

        Db::startTrans();
        try {
            $time     = time();
            $all_data = [];
            foreach ($excel_array as $key => $value) {
                $data = [];
                foreach ($field_list as $field_key => $field_value) {
                    if (!isset($value[$field_key])) {
                        throw new RuntimeException('第 ' . ($key + 2) . ' 行第 ' . $field_key . ' 列缺少数据');
                    }
                    $data[$field_value] = $value[$field_key];
                }
                // 有创建时间和更新时间的处理
                $data['create_time'] = $time;
                $data['update_time'] = $time;

                $all_data[] = $data;
            }

            Db::name($table)
                ->limit($limit)
                ->insertAll($all_data);
            Db::commit();
            $result = true;
        } catch (Exception $exception) {
            Db::rollback();
            $result = '导入失败，信息：' . $exception->getMessage();
        }
        if ($del) {
            unlink($path);
        }
        return $result;
    }

    /**
     * 下载导入模版
     * @param array $field_name_list 字段名称列表
     * @param string $name 文件名
     */
    protected function downloadExample(array $field_name_list, string $name = ''): Response
    {
        try {

            if (empty($name)) {
                $name = '导入模版' . date('Y-m-d');
            }

            [$spreadsheet, $sheet, $char_index] = $this->initSheet();

            // Excel 表格头
            foreach ($field_name_list as $key => $val) {
                $sheet->setCellValue($char_index[$key] . '1', $val);
            }

            $spreadsheet->getActiveSheet()->setTitle($name);

            $writer = new Xlsx($spreadsheet);

            $file_name = sha1($name . microtime());
            $file_path = base_path() . '/export/example/' . date('Ym') . '/';
            if (!is_dir($file_path) && !mkdir($file_path, 0777, true) && !is_dir($file_path)) {
                throw new RuntimeException(sprintf('Directory "%s" was not created', $file_path));
            }
            $save_name = $file_name . '.xlsx';
            // 保存文件到 public 下
            $writer->save($save_name);
            // 下载文件
            return response()->download($save_name, $name . '.xlsx');

        } catch (Exception $e) {
            return admin_error($e->getMessage() . '(' . $e->getFile() . '-' . $e->getLine() . ')');
        }
    }

    /**
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    protected function initSheet(): array
    {
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->setActiveSheetIndex(0);
        $char_index  = range('A', 'Z');

        // 处理超过26列
        $a = 'A';
        foreach ($char_index as $item) {
            $char_index[] = $a . $item;
        }

        return [$spreadsheet, $sheet, $char_index];
    }

    /**
     * 简单导出，占用内存低，导出1-10万条数据使用
     * @param $header
     * @param $body
     * @param string $name
     * @return Response
     */
    public function exportXlsx($header, $body, string $name = ''): Response
    {
        if (empty($name)) {
            $name = date('Y-m-d-H-i-s');
        }

        $writer = new XLSXWriter();
        $writer->writeSheetRow('Sheet1', $header);
        foreach ($body as $row) {
            $row_data = [];
            foreach ($row as $item) {
                $row_data[] = $item;
            }
            $writer->writeSheetRow('Sheet1', $row_data);
        }

        $file_name = sha1($name . microtime());
        $file_path = base_path() . '/export/' . $file_name . '.xlsx';
        // 保存文件到 public 下
        $writer->writeToFile($file_path);
        // 下载文件
        return response()->download($file_path, $name . '.xlsx');
    }
}
